﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Xml.Linq;
using Vroom.Common;
using Vroom.Common.Config;
using Vroom.Common.Enums;
using Vroom.Common.IFaces.Loader;
using Vroom.Common.IFaces.Utils.Logging;
using Vroom.Config;
using Vroom.Utils;
using log4net;
using LumenWorks.Framework.IO.Csv;

namespace Vroom.Loaders.Types
{
    [GuidAttribute("41C68A99-CDB6-4142-93E8-8E828922AF52")]
    public class SqlServerLoader : IDbLoader
    {
        #region Properties

        public IConfigItem  Config      { get; set; }
        public IConfigUtils ConfigUtils { get; set; }

        public Dictionary<string, string> Mappings          { get; set; }
        public List<string> SourceColumns                   { get; set; }
        public List<string> DestinationColumns              { get; set; }
        public Dictionary<string, string> ColumnOverrides   { get; set; }
        public List<string> ExplicitNullColumns             { get; set; }

        public DateTime StartDateTime       { get; set; }
        public DateTime EndDateTime         { get; set; }
        public Guid ClassGuid               { get { return GetType().GUID; } }
        public Guid ExecGuid                { get; set; }
        public IAuditLogger AuditLogger     { get; set; }
        public string Errors                { get; set; }

        private static readonly ILog logger = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType.Name);

        #endregion


        public SqlServerLoader(IConfigItem configItem, Guid execGuid)
        {
            Config = configItem;
            ConfigUtils = new ConfigUtils();

            #region Set Loggers

            ExecGuid = execGuid;
            log4net.ThreadContext.Properties["ExecGuid"] = ExecGuid.ToString();
            log4net.ThreadContext.Properties["ClassGuid"] = ClassGuid.ToString();
            StartDateTime = DateTime.UtcNow;
            AuditLogger = new AuditLogger(ClassGuid, ExecGuid, configItem.SourceShortName, configItem.Destination, GetType().Name);

            #endregion


            #region Set Dest Schema and perform Column Mapping            

            ColumnOverrides = new Dictionary<string, string>();
            ExplicitNullColumns = new List<string>();

            DestinationColumns = GetDestinationSchema();

            if (!string.IsNullOrEmpty(Config.MappingsOverrides)) { SetColumnsOverrides(); }
            if (!string.IsNullOrEmpty(Config.ExplicitNullColumns)) { SetExplicitNullColumns(); }

            #endregion


            #region Check if Date needs to be fetched from Db

            if (Config.Source.Contains(Consts.DWH_T_MINUS_1))
            {
                string prevWorkingDay = ConfigUtils.GetPreviousWorkingDayBySourceSystem(Config.SourceSystem);
                Config.Source = Config.Source.Replace(Consts.DWH_T_MINUS_1, prevWorkingDay);
                Config.SourceShortName = Config.Source.Substring(Config.Source.LastIndexOf(@"\")+1);
            }

            #endregion
        }


        private void SetColumnsOverrides()
        {
            ColumnOverrides = ConfigUtils.GetColumnsOverrides(Config);
        }


        public void SetExplicitNullColumns()
        {
            ExplicitNullColumns = ConfigUtils.GetExplicitNullColumns(Config);
        }


        public List<string> GetDestinationSchema()
        {
            return ConfigUtils.GetDestinationSchema(Config);
        }


        public Dictionary<string, Tuple<Type, int, int, int>> GetDestinationSchemaMetadata()
        {
            return ConfigUtils.GetDestinationSchemaMetadata(Config);
        }


        public Dictionary<string, Type> GetExtendedDestinationSchema()
        {
            return ConfigUtils.GetExtendedDestinationSchema(Config, ExplicitNullColumns);
        }


        public void TruncateDestinationTable()
        {

            using (var con = new SqlConnection(Config.DestinationConnectionString))
            {
                using (var cmd = new SqlCommand(Consts.SQL_TRUNCATE + Config.Destination, con))
                {
                    con.Open();
                    int affectedRows = cmd.ExecuteNonQuery();
                }
            }
        }


        public void BeginLoad()
        {
            StartDateTime = DateTime.UtcNow;
            AuditLogger.LogStart(StartDateTime);
            int rowsInserted = 0;

            CsvReader csv = null;
            SqlBulkCopy bulkCopy = null;

            try
            {
                Dictionary<string, Type> colDef = GetExtendedDestinationSchema();

                using (csv = new CsvReader(new StreamReader(Config.Source, Encoding.GetEncoding(1252)), true, colDef))
                {
                    using (bulkCopy = new SqlBulkCopy(Config.DestinationConnectionString, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.CheckConstraints))
                    {
                        #region Column Mappings and override Handling

                        //Map By Name
                        if (Config.ColumnMappingType == ColumnMappingType.Name)
                        {
                            foreach (string colName in DestinationColumns)
                            {
                                if (ColumnOverrides.ContainsValue(colName))
                                    bulkCopy.ColumnMappings.Add(ColumnOverrides.FirstOrDefault(p => p.Value == colName).Key, colName);
                                else
                                    bulkCopy.ColumnMappings.Add(colName, colName);
                            }
                        }

                        //Map By Order
                        if (Config.ColumnMappingType == ColumnMappingType.Order)
                        {
                            int colLoc = 0;
                            foreach (string colName in DestinationColumns)
                            {
                                bulkCopy.ColumnMappings.Add(colLoc, colLoc + 2);
                                colLoc++;
                            }
                        }

                        #endregion

                        //Set
                        bulkCopy.DestinationTableName = Config.Destination;
                        bulkCopy.BulkCopyTimeout = 600;
                        bulkCopy.BatchSize = 50000;

                        //Truncate
                        TruncateDestinationTable();

                        //Write
                        bulkCopy.WriteToServer(csv);

                        //Get Count for AuditLog
                        rowsInserted = (int)typeof(SqlBulkCopy).GetField("_rowsCopied", BindingFlags.NonPublic | BindingFlags.GetField | BindingFlags.Instance).GetValue(bulkCopy);
                    }
                }

                logger.Info("[ SRC:" + Config.SourceShortName + " | DEST:" + Config.Destination + " ]" + ", Inserted: " + rowsInserted);
                AuditLogger.LogEnd(ValidationStatus.Valid, "Rows Inserted:" + rowsInserted, StartDateTime, DateTime.UtcNow, string.Empty, rowsInserted, 0);
            }
            catch (FileNotFoundException fof)
            {
                Errors = fof.Message;
                string errMessage = "[ SRC:" + Config.SourceShortName + " | DEST:" + Config.Destination + " ]" + " ERROR: " + Consts.TWO_LINES + fof.Message + Consts.TWO_LINES + Errors + Consts.TWO_LINES + fof.StackTrace;
                logger.Error(errMessage, fof);
                AuditLogger.LogEnd(ValidationStatus.Invalid, "ERROR", StartDateTime, DateTime.UtcNow, errMessage, 0, 0);
                throw;
            }
            catch (Exception e)
            {
                string errMessage = string.Empty;

                if (bulkCopy != null || csv != null)
                {
                    rowsInserted = (int)typeof(SqlBulkCopy).GetField("_rowsCopied", BindingFlags.NonPublic | BindingFlags.GetField | BindingFlags.Instance).GetValue(bulkCopy);
                    Errors = GetInvalidFiledsOnLine(rowsInserted, csv.Delimiter);
                    Errors = (string.IsNullOrEmpty(Errors)) ? "No Errors found on Line " + (rowsInserted + 2) + ", please check around this Line number in file..." : Errors;
                }
                else
                {
                    rowsInserted = -3;
                    Errors = "Problem with Loading the file: " + Config.Source;
                }

                errMessage = "[ SRC:" + Config.SourceShortName + " | DEST:" + Config.Destination + " ]" + " ERROR: " + Consts.TWO_LINES + e.Message + Consts.TWO_LINES + "Invalid Line:  " + (rowsInserted + 2) + Consts.TWO_LINES + Errors + Consts.TWO_LINES + e.StackTrace;

                logger.Error(errMessage, e);
                AuditLogger.LogEnd(ValidationStatus.Invalid, "ERROR", StartDateTime, DateTime.UtcNow, errMessage, rowsInserted, 0);

                throw;
            }
        }


        private string GetInvalidFiledsOnLine(int rowsInserted, char delimiter)
        {
            string collapsedErrorsOnLine = string.Empty;
            List<string> errorsOnLine = new List<string>();
            DateTime SQL_MIN = new DateTime(1753, 1, 1);
            DateTime SQL_MAX = new DateTime(9999, 12, 31);

            try
            {
                Dictionary<string, string> linesWithHeader = new Dictionary<string, string>();
                Dictionary<string, Tuple<Type, int, int, int>> fieldDataTypeDef = GetDestinationSchemaMetadata();

                IEnumerable<string> headerLines = File.ReadLines(Config.Source).Take(1).First().Split(delimiter).Select(p => p.Replace(@"""", string.Empty));
                string culpritLine = File.ReadLines(Config.Source).Skip(rowsInserted + 1).Take(1).First();
                IEnumerable<string> fields = culpritLine.Split(delimiter).Select(p => p.Replace(@"""", string.Empty)).AsEnumerable();

                for (int i = 0; i < headerLines.Count(); i++)
                    linesWithHeader.Add(headerLines.ElementAt(i).ToString(), fields.Where((p, index) => index == i).FirstOrDefault().ToString());

                #region Traverse Fields And Validate

                foreach (string fieldNameInDbSchema in fieldDataTypeDef.Keys)
                {
                    string fieldNameInFile       = linesWithHeader[fieldNameInDbSchema];
                    Type fieldDataType           = fieldDataTypeDef[fieldNameInDbSchema].Item1;
                    int requiredFieldSize        = fieldDataTypeDef[fieldNameInDbSchema].Item2;
                    int requiredNumericPrecision = fieldDataTypeDef[fieldNameInDbSchema].Item3;
                    int requiredNumericScale     = fieldDataTypeDef[fieldNameInDbSchema].Item4;

                    if (string.IsNullOrEmpty(fieldNameInFile))
                    {
                        continue;
                    }
                    if (fieldDataType == typeof(int))
                    {
                        int result;
                        if (!int.TryParse(fieldNameInFile, out result)) { errorsOnLine.Add("Error in FieldName: {" + fieldNameInDbSchema + "}, Expected Type: " + fieldDataType + ", actual data in Field:" + fieldNameInFile); }
                    }
                    else if (fieldDataType == typeof(decimal))
                    {
                        decimal result;
                        if (!decimal.TryParse(fieldNameInFile, out result))
                            errorsOnLine.Add("Error in FieldName:" + fieldNameInDbSchema + ", Expected Type: {" + fieldDataType + "}, actual data in Field]:" + fieldNameInFile);
                        else
                        {
                            int actualNumericPrecision = fieldNameInFile.Length - 1;
                            int actualNumericScale = fieldNameInFile.Length - fieldNameInFile.IndexOf(".") - 1;

                            if (actualNumericPrecision > requiredNumericPrecision || actualNumericScale > requiredNumericScale)
                                errorsOnLine.Add("Error in FieldName: {" + fieldNameInDbSchema + "}, Expected Type: " + fieldDataType + " [Precision:" + requiredNumericPrecision +
                                                 ", Scale:" + requiredNumericScale + "], actual data in Field:" + fieldNameInFile);
                        }
                    }
                    else if (fieldDataType == typeof(DateTime))
                    {
                        DateTime result;
                        if (!DateTime.TryParse(fieldNameInFile, out result))
                            errorsOnLine.Add("Error in FieldName: {" + fieldNameInDbSchema + "}, Expected Type: " + fieldDataType + ", Actual data in Field:" + fieldNameInFile);
                        else
                            if (result < SQL_MIN || result > SQL_MAX)
                                errorsOnLine.Add("Error in FieldName: {" + fieldNameInDbSchema + "}, Expected Type: " + fieldDataType + ", Actual data in Field:" + fieldNameInFile + ", Value not between: " + SQL_MAX + " and " + SQL_MAX);

                    }
                    else if (fieldDataType == typeof(string))
                    {
                        if (fieldNameInFile.Length > requiredFieldSize) { errorsOnLine.Add("Error in FieldName: {" + fieldNameInDbSchema + "}, Expected Type: " + fieldDataType + ", Actual data in Field:" + fieldNameInFile + ", Field Length exceeds size of: " + requiredFieldSize); }
                    }
                    else if (fieldDataType == typeof(float))
                    {
                        float result;
                        if (!float.TryParse(fieldNameInFile, out result)) { errorsOnLine.Add("Error in FieldName: {" + fieldNameInDbSchema + "}, Expected Type: " + fieldDataType + ", Actual data in Field:" + fieldNameInFile); }
                    }
                    else if (fieldDataType == typeof(bool))
                    {
                        bool result;
                        if (!bool.TryParse(fieldNameInFile, out result)) { errorsOnLine.Add("Error in FieldName: {" + fieldNameInDbSchema + "}, Expected Type: " + fieldDataType + ", Actual data in Field:" + fieldNameInFile); }
                    }
                }

                #endregion

                collapsedErrorsOnLine = new string(errorsOnLine.SelectMany(i => i + Environment.NewLine + Environment.NewLine).ToArray());
            }
            catch (Exception e)
            {
                collapsedErrorsOnLine = "Failure in fetching Error on Line... Check for possible File corruption or metadata mismatches between File and Table Schema." + Environment.NewLine + Environment.NewLine + e.Message;
            }

            return collapsedErrorsOnLine;
        }

    }
}